package com.scienzeinternazionali.application;



	/*
	 * Licensed to the Apache Software Foundation (ASF) under one or more
	 * contributor license agreements.  See the NOTICE file distributed with
	 * this work for additional information regarding copyright ownership.
	 * The ASF licenses this file to You under the Apache License, Version 2.0
	 * (the "License"); you may not use this file except in compliance with
	 * the License.  You may obtain a copy of the License at
	 * 
	 *      http://www.apache.org/licenses/LICENSE-2.0
	 * 
	 * Unless required by applicable law or agreed to in writing, software
	 * distributed under the License is distributed on an "AS IS" BASIS,
	 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	 * See the License for the specific language governing permissions and
	 * limitations under the License.
	 */

	import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;

import com.scienzeinternazionali.exception.DataSourceException;
import com.scienzeinternazionali.util.LogFactory;





	//
//	 Here's a simple example of how to use the BasicDataSource.
//	 In this example, we'll construct the BasicDataSource manually,
//	 but you could also configure it using an external conifguration file.
//

//
//	 Note that this example is very similiar to the PoolingDriver
//	 example.

//
//	 To compile this example, you'll want:
//	  * commons-pool-1.3.jar
//	  * commons-dbcp-1.2.2.jar
//	  * j2ee.jar (for the javax.sql classes)
//	 in your classpath.
//
//	 To run this example, you'll want:
//	  * commons-pool-1.3.jar
//	  * commons-dbcp-1.2.2.jar
//	  * j2ee.jar (for the javax.sql classes)
//	  * the classes for your (underlying) JDBC driver
//	 in your classpath.
//
//	 Invoke the class using two arguments:
//	  * the connect string for your underlying JDBC driver
//	  * the query you'd like to execute
//	 You'll also want to ensure your underlying JDBC driver
//	 is registered.  You can use the "jdbc.drivers"
//	 property to do this.
//
//	 For example:
//	  java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver \
//	       -classpath commons-pool-1.3.jar:commons-dbcp-1.2.2.jar:j2ee.jar:oracle-jdbc.jar:. \
//	       ManualPoolingDataSourceExample
//	       "jdbc:oracle:thin:scott/tiger@myhost:1521:mysid"
//	       "SELECT * FROM DUAL"
//
	
public class ConnectionManager {
	private static Logger logger =  LogFactory.getLogger(LogFactory.SCIENZEINTERNAZIONALI);
	
	  private static String dbUrl;           // Il nome del database
	  private static String dbDriver;        // Il driver del database
	  private static String dbLogin;         // Il login per il database
	  private static String dbPassword;      // La password di accesso al database
      static ResourceBundle connessione = ResourceBundle.getBundle("connection");
      private static DataSource dataSource;

public static void main(String[] args) {
    // First we set up the BasicDataSource.
    // Normally this would be handled auto-magically by
    // an external configuration, but in this example we'll
    // do it manually.
    //
	
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
  try{  
//    System.out.println("Setting up data source.");
    dataSource = setupDataSource();
//    System.out.println("Done.");
//    System.out.println("Creating connection.");
    conn = getConnection();
//    System.out.println("Creating statement.");
    stmt = conn.createStatement();
//    System.out.println("Executing statement.");
    rset = stmt.executeQuery("SELECT count(*) FROM user");
//    System.out.println("Results:");
    int numcols = rset.getMetaData().getColumnCount();
    while(rset.next()) {
        for(int i=1;i<=numcols;i++) {
            System.out.print("\t" + rset.getString(i));
        }
//        System.out.println("");
    }
} catch(DataSourceException e) {
    e.printStackTrace();
} catch(Exception e) {
    e.printStackTrace();
} finally {
    try { rset.close(); } catch(Exception e) { }
    try { stmt.close(); } catch(Exception e) { }
    try { conn.close(); } catch(Exception e) { }
}
    //
    // Now, we can use JDBC DataSource as we normally would.
    //
}

public static Connection getConnection() throws DataSourceException {
    Connection conn = null;
    try {
    	dataSource = setupDataSource();
        conn = dataSource.getConnection();
    }catch(Exception e) {
    	logger.fatal(e.getMessage());
        e.printStackTrace();
        throw new DataSourceException("errore generale nella connessione al database");
    }
	return conn;
}
public static DataSource setupDataSource() throws DataSourceException{
    BasicDataSource ds = new BasicDataSource();
    loadParameters();
    ds.setDriverClassName(dbDriver);
    ds.setUsername(dbLogin);
    ds.setPassword(dbPassword);
    ds.setUrl(dbUrl);
    return ds;
}

public static void printDataSourceStats(DataSource ds) throws SQLException {
    BasicDataSource bds = (BasicDataSource) ds;
//    System.out.println("NumActive: " + bds.getNumActive());
//    System.out.println("NumIdle: " + bds.getNumIdle());
}

public static void shutdownDataSource(DataSource ds) throws SQLException {
    BasicDataSource bds = (BasicDataSource) ds;
    bds.close();
}

public static void closeAll(ResultSet rset, PreparedStatement pst,  Connection conn){
	if(rset != null){
		try {
    		rset.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	if(pst != null){
		try {
    		pst.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	if(conn != null){
		try {
    		conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
public static void closeAll(ResultSet rset, Statement st,  Connection conn){
	if(rset != null){
		try {
    		rset.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	if(st != null){
		try {
    		st.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	if(conn != null){
		try {
    		conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

  private static void loadParameters() {
		logger.debug("loadParameters BEGIN");
	    dbUrl = connessione.getString("dbUrl");  
	    dbDriver = connessione.getString("dbDriver"); 
	    dbLogin = connessione.getString("dbLogin"); 
	    dbPassword = connessione.getString("dbPassword");
		logger.debug("loadParameters END");
	  }	    
}
/*
 * "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=127.0.0.1;" _
            & "DATABASE=test;" _
            & "UID=testuser;" _
            & "PWD=12345;" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
 * */
